﻿using System;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GAMS.MODEL;
using System.Configuration;

namespace KPIS.GERP.GAMS.DAL
{
    public class D_ACM_MiscReport
    {
        DBManager dbManager = new DBManager(SystemInfo._DataProvider, ConfigurationSettings.AppSettings["ConnectionString"].ToString());
        ConvertNullable con = new ConvertNullable();

        public DataTable ReportAdjust(string tran_seq)
        {
            DataTable dtb = null;
            string strSQL = "";
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@tran_seq", tran_seq);

                strSQL = @"
select acc.account_name, null, null, null, null, null, acc.account_code, null
, case when td.dr_or_cr = 'Dr' then td.amount else null end as dr, null, null
, case when td.dr_or_cr = 'Cr' then td.amount else null end as cr

from acm_transection_dtls td
left join acm_mas_account acc on td.account_seq = acc.account_seq
where td.record_status <> @delete_status
and td.tran_seq = @tran_seq
ORDER BY td.dr_or_cr desc, acc.account_code";

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                return dtb;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable ReportRQ_PQ_DN(string bgm_source_seq, string ref_no_start, string ref_no_end
            , DateTime? dtStart, DateTime? dtEnd)
        {
            DataTable dtb = null;
            string strSQL = "";
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(6);
                dbManager.AddParameters(0, "@approve_status", SystemInfo._ApproveRecordStatus);
                dbManager.AddParameters(1, "@bgm_source_seq", bgm_source_seq);
                dbManager.AddParameters(2, "@ref_no_start", ref_no_start);
                dbManager.AddParameters(3, "@ref_no_end", ref_no_end);
                dbManager.AddParameters(4, "@dtStart", dtStart);
                dbManager.AddParameters(5, "@dtEnd", dtEnd);

                strSQL = @"
select mr.ref_no as rq
, mr.money_req_date as rq_date
, mr.money_req_name as rq_name
,	mrd.amount
, p.ref_no as pq
, p.pay_date as pq_date
, dn.Code as dn
, dn.DeliveryDate as dn_date

from fim_money_requests mr
left join (select money_req_seq
	, sum(coalesce(amount, 0)) as amount
	from fim_money_request_dtls
	where record_status <> 'D'
	group by money_req_seq
	) mrd on mr.money_req_seq = mrd.money_req_seq
left join fim_payments p on mr.money_req_seq = p.money_req_seq and p.record_status = 'A'
left join IcmDeliveryNote dn on mr.icm_dn_seq = dn.DnId and dn.RecordStatus = 'A'

where mr.record_status = 'A'"
                    + (!string.IsNullOrEmpty(bgm_source_seq) ? " and mr.bgm_source_seq = @bgm_source_seq" : "")
                    + (dtStart.HasValue ? " and mr.money_req_date >= @dtStart" : "")
                    + (dtEnd.HasValue ? " and mr.money_req_date <= @dtEnd" : "")
                    + (!string.IsNullOrEmpty(ref_no_start) ? " and mr.ref_no >= @ref_no_start" : "")
                    + (!string.IsNullOrEmpty(ref_no_end) ? " and mr.ref_no <= @ref_no_end" : "")
                    + " ORDER BY mr.ref_no";

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                return dtb;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable ReportRemainCollateral(DateTime date)
        {
            DataTable dtb = null;
            string strSQL = "";
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@date", date);

//                strSQL = @"
//select coalesce(r.receive_date, col.CreatedWhen) as receive_date, po.Code as PoCode
//, po.Name as PoName
//, c.NameTh, col.ReturnDueDate, col.Amount
//from IcmBidderCollateral col
//left join IcmBidder b on col.BidderId = b.BidderId
//left join IcmPurchaseOrder po on b.BidderId = po.BidderId
//left join IcmMasContact c on b.ConId = c.ConId
//left join fim_receives r on col.BidderColId = r.icm_po_con_col_seq and r.record_status not in ('D', 'C')
//
//where (po.RecordStatus <> 'D' or po.PoId is null)
//and coalesce(r.receive_date, col.CreatedWhen) <= @date
//and col.ColStatusId = 31
//ORDER BY r.receive_date";
                strSQL = @"
select po.Code, c.NameTh, r.receive_date as BidderColDate
, r.receipt_no as r_ref_no
, r.amount as r_amount, col.ReturnDueDate

, p.pay_date
, p.ref_no as p_ref_no

, coalesce(r.amount, 0) - coalesce(p.amount, 0) as total
--, look.LookUpName
--, coalesce(r.amount, 0) - coalesce(p.amount, 0) as total
--, p.amount as p_amount
from fim_receives r 
inner join IcmBidderCollateral col on r.icm_po_con_col_seq = col.BidderColId 
left join IcmMasLookUp look on col.ColStatusId = look.LookUpId
left join IcmMasContact c on r.contact_seq = c.ConId
left join IcmPurchaseOrder po on col.BidderId = po.BidderId and po.RecordStatus in ('A', 'J')
left join (select quo.BidderId, sum(quo.NetAmount) as amount
	from IcmBidderQuotation quo
	group by quo.BidderId
	) bid on po.BidderId = bid.BidderId
left join (select p.pay_seq, p.amount, p.ref_no
, case when p.method_seq in (1, 3)
	then p.real_pay_date
	else case when p.method_seq = 2
		then chq.pay_date
		else pip.pip_date
		end
	end as pay_date
from fim_payments p
left join fim_payment_cheques pc on p.pay_seq = pc.pay_seq
left join fim_cheques chq on pc.cheque_seq = chq.cheque_seq
left join fim_payments_payinpack pip on p.pay_seq = pip.pay_seq
where p.record_status = 'A'
) p on r.pay_seq = p.pay_seq and p.pay_date is not null and p.pay_date <= @date
where r.record_status not like 'D'
and r.receive_date <= @date
and p.pay_seq is null
order by r.receive_date
";

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                return dtb;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable ReportRemainBorrow(string bgm_source_seq, DateTime date)
        {
            DataTable dtb = null;
            string strSQL = "";
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@bgm_source_seq", bgm_source_seq);
                dbManager.AddParameters(1, "@date", date);

                strSQL = @"
select *
from (select 
case when p.method_seq in (1, 3) then p.real_pay_date
	else case when p.method_seq = 2 then chq.cheque_date
		else case when p.method_seq = 4 then pip.pip_date
			else null END
		END
	end as pay_date
, mr.borrow_no, p.ref_no, pay_name, p.amount, mr.borrow_return_date
, concat(np.prefix_name, emp.first_name, ' ', emp.last_name) as emp_name

, rt.amount as return_amount
, rec.amount as receive_amount
, cast(coalesce(p.amount, 0) as decimal(18,2)) - cast(coalesce(rt.amount, 0) as decimal(18,2)) - cast(coalesce(rec.amount, 0) as decimal(18,2)) as remain_amount
from fim_payments p
left join fim_money_requests mr on p.money_req_seq = mr.money_req_seq
left join hrm_ps_employees emp on p.emp_seq = emp.emp_seq
left join mas_name_prefixes np on emp.name_prefix_seq = np.name_prefix_seq

left join (select pc.pay_seq, min(chq.pay_date) as cheque_date
	from fim_payment_cheques pc
	left join fim_cheques chq on pc.cheque_seq = chq.cheque_seq
	where pc.record_status <> 'D'
	and chq.pay_date is not null
	group by pc.pay_seq
) chq on p.pay_seq = chq.pay_seq

left join fim_payments_payinpack pip on p.pay_seq = pip.pay_seq

left join (select rt.money_req_seq, min(rt.ref_no) as return_ref_no
	, min(rt.return_date) as return_date 
	, sum(coalesce(rd.amount, 0)) as amount
	from fim_money_return_dtls rd
	left join fim_money_returns rt on rd.return_seq = rt.return_seq
	where rd.record_status not like 'D'
	and rt.record_status not like 'D'
	and rt.return_date <= @date
	group by rt.money_req_seq
	) rt on mr.money_req_seq = rt.money_req_seq

left join (select r.money_req_seq, min(r.ref_no) as receive_ref_no
	, min(r.receive_date) as receive_date 
	, sum(coalesce(r.amount, 0)) as amount
	from fim_receives r
	where r.record_status not in ('D', 'C')
	and r.receive_date <= @date
	group by r.money_req_seq
	) rec on mr.money_req_seq = rec.money_req_seq
where p.record_status = 'A'
and p.event_seq = 16"
+ (!string.IsNullOrEmpty(@bgm_source_seq) ? " and p.bgm_source_seq = @bgm_source_seq" : "")

+ @"
)t
where remain_amount > 0
and pay_date <= @date
order by t.borrow_no
;";

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                return dtb;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable ReportRemainExpense(string bgm_source_seq, string event_seq, DateTime date)
        {
            DataTable dtb = null;
            string strSQL = "";
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);
                dbManager.AddParameters(0, "@bgm_source_seq", bgm_source_seq);
                dbManager.AddParameters(1, "@date", date);
                dbManager.AddParameters(2, "@event_seq", event_seq);

                strSQL = @"
select mr.ref_no, mr.money_req_date
, concat(mr.money_req_name, ' (', case when mr.contact_seq is not null then c.NameTh else concat(np.prefix_name, emp.first_name, ' ', emp.last_name) end, ')') as money_req_name
, mrd.amount
from fim_money_requests mr
left join (select mrd.money_req_seq
	, sum(coalesce(mrd.amount, 0)) as amount
	from fim_money_request_dtls mrd
	where mrd.record_status <> 'D'
	group by mrd.money_req_seq
	) mrd on mr.money_req_seq = mrd.money_req_seq
left join (select p.money_req_seq, p.pay_seq
	, case when p.method_seq in (1, 3) then p.real_pay_date
		else case when p.method_seq = 2 then chq.cheque_date
			else case when p.method_seq = 4 then pip.pip_date
				else null END
			END
		end as pay_date
	from fim_payments p
	left join (select pc.pay_seq, min(chq.pay_date) as cheque_date
		from fim_payment_cheques pc
		left join fim_cheques chq on pc.cheque_seq = chq.cheque_seq
		where pc.record_status <> 'D'
		and chq.pay_date is not null
		group by pc.pay_seq
	) chq on p.pay_seq = chq.pay_seq

	left join fim_payments_payinpack pip on p.pay_seq = pip.pay_seq
	where p.record_status = 'A'
) p on mr.money_req_seq = p.money_req_seq and p.pay_date is not null and p.pay_date <= @date
left join IcmMasContact c on mr.contact_seq = c.ConId
left join hrm_ps_employees emp on mr.emp_seq = emp.emp_seq
left join mas_name_prefixes np on emp.name_prefix_seq = np.name_prefix_seq

where mr.record_status = 'A'
and p.pay_seq is null
and mr.money_req_date <= @date"
                    + (!string.IsNullOrEmpty(event_seq) ? " and mr.event_seq in (" + event_seq + ")" : "")
                    + (!string.IsNullOrEmpty(bgm_source_seq) ? " and mr.bgm_source_seq = @bgm_source_seq" : "")
;

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                return dtb;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable ReportRemainBuyVat(string bgm_source_seq, DateTime date)
        {
            DataTable dtb = null;
            string strSQL = "";
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@bgm_source_seq", bgm_source_seq);
                dbManager.AddParameters(1, "@date", date);

                strSQL = @"
--select distinct *
--from (
select ref_no as ref_no, money_req_date, money_req_name, amount, amount_vat
from(
select mr.ref_no, mr.money_req_date, mr.money_req_name, mrd.amount, mrd.amount_vat
from fim_money_requests mr
left join (select mrd.money_req_seq
    , sum(coalesce(mrd.amount_vat, 0)) as amount_vat
	, sum(coalesce(mrd.amount, 0)) as amount
	from fim_money_request_dtls mrd
	where mrd.record_status <> 'D'
	group by mrd.money_req_seq
	) mrd on mr.money_req_seq = mrd.money_req_seq
left join (select p.money_req_seq
	, case when p.method_seq in (1,3) then p.real_pay_date
		else case when p.method_seq = 2 then chq.pay_date
			else case when p.method_seq = 4 then pip.pip_date
				else null
				end
			END
		end as pay_date
	from fim_payments p
	left join fim_payment_cheques pc on p.pay_seq = pc.pay_seq and pc.record_status <> 'D'
	left join fim_cheques chq on pc.cheque_seq = chq.cheque_seq
	left join fim_payments_payinpack pip on p.pay_seq = pip.pay_seq and pip.record_status <> 'D'
	where p.record_status = 'A'
) p on mr.money_req_seq = p.money_req_seq and p.pay_date <= @date
left join IcmDeliveryNote dn on mr.icm_dn_seq = dn.DnId
where mr.record_status = 'A'
and p.money_req_seq is null 
and mrd.amount_vat > 0
and mr.money_req_date <= @date
and dn.DnId is null"
                    + (!string.IsNullOrEmpty(bgm_source_seq) ? " and mr.bgm_source_seq = @bgm_source_seq" : "")

+ @"
union all
select dn.Code as ref_no, dn.ReceiveDate as money_req_date, dn.Name as money_req_name
, dni.Amount as amount, dni.VatAmount as amount_vat
from IcmDeliveryNote dn
left join (
	select DnId
	, sum(coalesce(NetAmount, 0)) as Amount
	, sum(coalesce(VatAmount, 0)) as VatAmount
	from IcmDeliveryNoteItem dni
	--where dni.RecordStatus <> 'D'
	group by DnId
	) dni on dn.DnId = dni.DnId
left join fim_money_requests mr on dn.DnId = mr.icm_dn_seq and mr.record_status = 'A'
left join v_payment p on mr.money_req_seq = p.money_req_seq and p.record_status = 'A' and p.real_pay_date2 <= @date
where dn.RecordStatus = 'A'
and p.pay_seq is null 
and dni.VatAmount > 0
and dn.ReceiveDate <= @date"
                    + (!string.IsNullOrEmpty(bgm_source_seq) ? " and mr.bgm_source_seq = @bgm_source_seq" : "")
+ @"
) t
--) t
order by t.money_req_date"

;

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                return dtb;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable ReportRemainCreditor(string bgm_source_seq, DateTime date)
        {
            DataTable dtb = null;
            string strSQL = "";
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@bgm_source_seq", bgm_source_seq);
                dbManager.AddParameters(1, "@date", date);

                strSQL = @"
select r.ref_no, r.receive_date
, concat(r.receipt_no, ' (', case when r.contact_seq is not null then c.NameTh else concat(np.prefix_name, emp.first_name, ' ', emp.last_name) end, ')') as receive_name
, r.amount
from fim_receives r
left join IcmMasContact c on r.contact_seq = c.ConId
left join hrm_ps_employees emp on r.emp_seq = emp.emp_seq
left join mas_name_prefixes np on emp.name_prefix_seq = np.name_prefix_seq
left join (select p.pay_seq
	, case when p.method_seq in (1, 3) then p.real_pay_date
		else case when p.method_seq = 2 then chq.cheque_date
			else case when p.method_seq = 4 then pip.pip_date
				else null END
			END
		end as pay_date
	from fim_payments p
	left join (select pc.pay_seq, min(chq.pay_date) as cheque_date
		from fim_payment_cheques pc
		left join fim_cheques chq on pc.cheque_seq = chq.cheque_seq
		where pc.record_status <> 'D'
		and chq.pay_date is not null
		group by pc.pay_seq
	) chq on p.pay_seq = chq.pay_seq

	left join fim_payments_payinpack pip on p.pay_seq = pip.pay_seq
	where p.record_status = 'A'
) p on r.pay_seq = p.pay_seq and p.pay_date is not null and p.pay_date <= @date
where r.receive_type_seq = 1051
and r.receive_mode = 2
and r.record_status not in ('D', 'C')
and p.pay_seq is null
and r.receive_date <= @date"
                    + (!string.IsNullOrEmpty(bgm_source_seq) ? " and r.bgm_source_seq = @bgm_source_seq" : "")
;

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                return dtb;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
        }
    }
}
